package fashionshop.dal;

import java.math.BigDecimal;
import java.sql.ResultSet;

import sun.security.jca.GetInstance;

import database.MySQLConnect;
import fashionshop.bll.OrderBLL;
import fashionshop.bll.ProductBLL;
import fashionshop.bll.StaffBLL;

public class OrderDAL {
	
	public OrderDAL(){
	}
	
	public static ResultSet LoadAll() throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery = "SELECT purchaseorder.*, customer.ID as CustomerID, customer.Name as CustomerName FROM purchaseorder, customer WHERE purchaseorder.CustomerID = customer.ID";
		return conn.executeQuery(sqlQuery);
	}
	
	public static ResultSet LoadByID(int id) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT purchaseorder.*, customer.Name as CustomerName FROM purchaseorder, customer WHERE purchaseorder.CustomerID = customer.ID and purchaseorder.ID = '%d'";
		String sqlQuery = String.format(sql, id);
		return conn.executeQuery(sqlQuery);
	}
	
	// Advance Search
	public static ResultSet LoadByCriteria(OrderBLL order, String customerName, String orderDateFrom, String orderDateTo, String paymentDateForm, String paymentDateTo) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sql = "SELECT purchaseorder.*, customer.Name as CustomerName FROM purchaseorder, customer WHERE purchaseorder.CustomerID = customer.ID and (customer.Name LIKE '%" + customerName + "%' and purchaseorder.Note LIKE '%" + order.getNote() + "%'";
		if(orderDateFrom != null)
			sql = sql +" and OrderDate >= '" + orderDateFrom + "'";
		if(orderDateTo != null)
			sql = sql +" and OrderDate <= '" + orderDateTo + "'";
		if(paymentDateForm != null)
			sql = sql +" and PaymentDate >= '" + paymentDateForm + "'";
		if(paymentDateTo != null)
			sql = sql +" and PaymentDate <= '" + paymentDateTo + "'";
		sql = sql + ")";
		System.out.println(sql);
		return conn.executeQuery(sql);
	}

	public static int Insert(OrderBLL order) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery;
		if(order.getPaymentDate().equals("")){
			String sql = "INSERT INTO purchaseorder (`CustomerID`, `Sum`, `Discount`, `OrderDate`, `Note`, `Status`) VALUES ('%d', '%.0f', '%.0f', '%s', '%s', '%s')";
			sqlQuery = String.format(sql, order.getCustomerId(), order.getSum(), order.getDiscount(), order.getOrderDate(), order.getNote(), order.getStatus());
		}
		else{
			String sql = "INSERT INTO purchaseorder (`CustomerID`, `Sum`, `Discount`, `OrderDate`, `PaymentDate`, `Note`, `Status`) VALUES ('%d', '%.0f', '%.0f', '%s', '%s', '%s', '%s')";
			sqlQuery = String.format(sql, order.getCustomerId(), order.getSum(), order.getDiscount(), order.getOrderDate(), order.getPaymentDate(), order.getNote(), order.getStatus());
		}
		return conn.executeUpdate(sqlQuery);
	}
	
	public static int Update(OrderBLL order) throws Exception{
		MySQLConnect conn = new MySQLConnect();
		String sqlQuery;
		if(order.getPaymentDate().equals("")){
			String sql = "UPDATE purchaseorder SET `CustomerID`='%d', `Sum`='%.0f', `Discount`='%.0f', `Note`='%s', `Status`='%s' WHERE `ID`='%d'";
			sqlQuery = String.format(sql, order.getCustomerId(), order.getSum(), order.getDiscount(), order.getNote(), order.getStatus(), order.getId());
		}
		else{
			String sql = "UPDATE purchaseorder SET `CustomerID`='%d', `Sum`='%.0f', `Discount`='%.0f', `PaymentDate`='%s', `Note`='%s', `Status`='%s' WHERE `ID`='%d'";
			sqlQuery = String.format(sql, order.getCustomerId(), order.getSum(), order.getDiscount(), order.getPaymentDate(), order.getNote(), order.getStatus(), order.getId());
		}
		return conn.executeUpdate(sqlQuery);
	}
}
